﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using System.Text;
using ReflectionStudio.Core.Events;
using INTEX.DataBase.Tools;
namespace WPFExcelReport
{

    public class JLTEKGBData : BaseFunction
    {
        public ApplicationBase app
        {
            get
            {
                return ApplicationBase.Instance;
            }
        }
        public INTEXDBC INTEXDBC
        {
            get
            {
                return app.INTEXDBC;
            }
        }
        public INTEXBL INTEXBL
        {
            get
            {
                return app.INTEXBL;
            }
        }
        public JLTEKGBData()
            : base()
        {

        }
        public JLTEKGBData(Dictionary<string, object> DicGlobalPara)
            : base(DicGlobalPara)
        {
        }

        public DataSet GetEKGB(DataRow dwABPOS)
        {
            DataSet Result;
            DataSet Return = new DataSet();
            string TableName = "WASTAMM";
            string FROMDNDATE = dwABPOS["FROMDNDATE"].ToString().Trim();
            string ENDDNDATE = dwABPOS["ENDDNDATE"].ToString().Trim();
            string FROMDATE = dwABPOS["FROMDATE"].ToString().Trim();
            string ENDDATE = dwABPOS["ENDDATE"].ToString().Trim();
            string FKRS = dwABPOS["FKRS"].ToString().Trim();
            string FLFD = dwABPOS["FLFD"].ToString().Trim();
            string SUPP = dwABPOS["SUPP"].ToString().Trim();
            string ARTICLE = dwABPOS["ARTICLE"].ToString().Trim();
            try
            {
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT * FROM ( "
                    + " SELECT DISTINCT A.EKKAUFKRS||'-'||TO_NUMBER(A.EKKAUFLFD) AS ORDERNO, "
                    + " TO_NUMBER(A.EKKLIEFNR) AS SUPP,A.EKKBDAT,TRIM(C.LSNAME2) AS SUPPNAME, "
                    + " TRIM(utl_raw.cast_to_varchar2(B.EKPART)) AS ARTICLE,G.GSHANDLNR,B.EKPME1*0.01 AS QTY, "
                    + " D.GPEINBRUT*0.01 as INQTY, "
                    + " D.GPEINDAT,B.EKPPRME*0.001 AS PRICE,TRIM(A.EKKKNTRNRL)||TRIM(EKKAKKRED)||TRIM(EKKZEICH) AS SCHEDNO, "
                    + " D.GPPARTIE AS LOT,D.GPSPPART AS SPART, "
                    + " d.gpfibl130*0.1 as AMM,d.gpfibl140*0.1 as BMM ,d.gpfibl11p*0.01 as CMM, "
                    + " d.GPFIBL12P*0.01 AS DMM,d.GPBFORCE*0.1 AS AVCN,d.GPBFORCECV*0.01 AS DIFCN, "
                    + " d.GPELONG*0.01 AS MINCN,d.GPUCV*0.01 AS CV,d.GPTHINP40 AS THINA, "
                    + " d.GPTHINP50 AS THINB,d.GPTHICKP35 AS THICKA,d.GPTHICKP50 AS THICKB, "
                    + " d.GPNEPS140 AS NEPSA,d.GPNEPS200 AS NEPSB,d.GPHAIR*0.01 AS HAIR, "
                    + " d.GPUINDEX*0.001 AS UINDEX,d.GPTITER*0.0001 AS TITER, "
                    + " d.GPENDBKG*0.01 AS ENDB,d.GPENDBHKM*0.01 AS ENDH, "
                    + " d.GPCOMMENT AS REMARK "
                    + " FROM EKKOPF A,EKPOS B,LIEFDAT C,GPDAT D,GSDAT G "
                    + " WHERE A.EKKMANDANT = B.EKPMANDANT AND A.EKKAUFKRS = B.EKPAUFKRS "
                    + " AND A.EKKAUFLFD = B.EKPAUFLFD AND A.EKKMANDANT = C.LSMANDANT "
                    + " AND B.EKPMANDANT = G.GSMANDANT AND utl_raw.cast_to_varchar2(B.EKPART) = G.GSART "
                    + " AND utl_raw.cast_to_varchar2(B.EKPVAR) = G.GSVAR "
                    + " AND A.EKKLIEFNR = C.LSLIEFNR AND B.EKPMANDANT = D.GPMANDANT(+) "
                    + " AND B.EKPAUFKRS = D.GPBESTKRS(+) AND B.EKPAUFLFD = D.GPBESTLFD(+) "
                    + " AND B.EKPAUFPOS = D.GPBESTPOS(+) AND B.EKPSA = 'PO' "
                    + " AND ( A.EKKAUFKRS = '" + FKRS + "' OR '" + FKRS + "'='AA' ) "
                    + " AND ( A.EKKAUFLFD = '" + FLFD + "' OR '" + FLFD + "'='000000' ) "
                    + " AND ( A.EKKLIEFNR = '" + SUPP + "' OR '" + SUPP + "'='00000000' ) "
                    + " AND A.EKKBDAT >=" + FROMDATE + " AND A.EKKBDAT <=" + ENDDATE + " ) "
                    + " WHERE ((GPEINDAT >=" + FROMDNDATE + "  AND GPEINDAT <=" + ENDDNDATE + ") OR " + FROMDNDATE + "=0) "
                    + " AND ARTICLE LIKE '%"+ARTICLE+"%' "
                    + " order by GPEINDAT,ORDERNO "
                    ;

                Result = Connect2Proxy(orlReadCommand);
                if (Result.Tables.Count > 0)
                {
                    Result.Tables[0].TableName = TableName;
                    byte[] b;
                    string value;
                    foreach (DataRow dw in Result.Tables[TableName].Rows)
                    {
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["SUPPNAME"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["SUPPNAME"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["GSHANDLNR"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["GSHANDLNR"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["REMARK"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["REMARK"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["SPART"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["SPART"] = value;
                    }
                }
                Return.Merge(Result);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return Return;
        }       
        public DataSet BuildJLTEKGBData(DataRow dwUI)
        {
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            try
            {
                setResult = GetEKGB(dwUI);
                setReturn.Merge(setResult);                
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }
    }
}